package com.webvihar.access2db.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

import com.google.gson.Gson;
import com.webvihar.access2db.model.JsonObject;
import com.webvihar.access2db.model.Table;


@Path("/runQuery")
public class ExecuteQuery {

		// This method is called if Application/JSON is request
		@GET
		@Produces(MediaType.APPLICATION_JSON)
		public String runResponse(String host, String port, String username, String password, String dbname, String query) {
			Table table = null;			
			String meta = "";
			String error = "";
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				String conString = "jdbc:mysql://"+host+":"+port+"/"+dbname;
				Connection con = DriverManager.getConnection(conString, username, password);
				Statement stmt = con.createStatement();
				if(query.toLowerCase().startsWith("select") || query.toLowerCase().startsWith("show")) {
					ResultSet rs = stmt.executeQuery(query);
					table = new Table(getHeaders(rs), getData(rs));
				} else {
					stmt.executeUpdate(query);
					table = null; 
				}
			} catch(Exception e) {
				e.printStackTrace();
				error = e.getMessage();
			}
			
			JsonObject<Table> response = new JsonObject<Table>(0, table, meta, error);
			Gson gson = new Gson();
			String responseJson = gson.toJson(response);
			
			return responseJson;
		}
		
		public String[] getHeaders(ResultSet rs) throws SQLException {
			ResultSetMetaData rsmeta = rs.getMetaData();
			String [] headers = new String[rsmeta.getColumnCount()];
			for(int i=1; i<=headers.length; i++) {
				headers[i] = rsmeta.getColumnName(i);
			}
			return headers;
		}
		
		public String[][] getData(ResultSet rs) throws SQLException {
			int noOfColumns = rs.getMetaData().getColumnCount();
			List<String[]> dataList = new ArrayList<String[]>();
			while(rs.next()) {
				String [] row = new String[noOfColumns];
				for(int i=1; i<=noOfColumns; i++) {
					row[i] = rs.getString(i);
				}
				dataList.add(row);
			}
			
			// convert list to array
			String[][] data = new String[dataList.size()][noOfColumns];
			for(int i=0; i<dataList.size(); i++) {
				data[i] = dataList.get(i);
			}
			return data;
		}
}
